package Actions;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.sql.*;

import org.apache.struts.action.*;

import forms.degreecollection;


public class LogonAction extends Action 
{
	public ActionForward execute(ActionMapping mapping, ActionForm form, 
		    HttpServletRequest request, HttpServletResponse response) {
		
		forms.LogonForm lo = (forms.LogonForm) form;
		
		String uname = (String) request.getSession().getAttribute("uname");
		
		String url = "jdbc:postgresql://localhost:5432/Applications";
		Connection conn = null;
		Statement stmt = null;
		Statement stmt1 = null;
		Statement stmt2 = null;
		Statement stmt3 = null;
		ResultSet rs = null;
		ResultSet rs1 = null;
		ResultSet rs2 = null;
		ResultSet rs3 = null;

		boolean ret = false;
		int app = -1;
		
		try {
			// Registering Postgresql JDBC driver
			Class.forName("org.postgresql.Driver");
			// Open a connection to the database	
			conn = DriverManager.getConnection(url,"postgres","postgres");
			// Create the statement
		    stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
		                				ResultSet.CONCUR_UPDATABLE);
			
			rs = stmt.executeQuery("SELECT user_name,APP FROM users WHERE user_name = '" + uname + "'");
			while( rs.next() )
			{
				if( rs.getInt("APP") > 0 )
				{
					app = rs.getInt("APP");
					ret = true;
				}
				else
				{
					ret = false;
				}
			}
			
		} catch (SQLException e) 
			{ 
				System.out.println(e);
			} catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				}


		if (!ret) {	
			
			return (mapping.findForward("newApplicant"));	
		}
		else {	

			
			// Registering Postgresql JDBC driver
			try {
				Class.forName("org.postgresql.Driver");
				// Open a connection to the database	
				conn = DriverManager.getConnection(url,"postgres","postgres");
				// Create the statement
			    stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
			                				ResultSet.CONCUR_UPDATABLE);
			    stmt1 = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
        				ResultSet.CONCUR_UPDATABLE);
			    stmt2 = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
        				ResultSet.CONCUR_UPDATABLE);
			    stmt3 = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
        				ResultSet.CONCUR_UPDATABLE);
				String first =null;
				String last = null;
				String middle = null;
			    rs = stmt.executeQuery("SELECT First, Middle, Last FROM NAME WHERE NameID ='"+app+"'");
			    
				while( rs.next() ){
					first = rs.getString("First");
					middle = rs.getString("Middle");
					last = rs.getString("Last");
				}

				rs = stmt.executeQuery("SELECT MainTable.AppID FROM MainTable WHERE NameID = '"
										+app+"'");
				int  meantableAppID= -1;
				while( rs.next() ){
					meantableAppID = rs.getInt("AppID");
				}
				
				
				rs = stmt.executeQuery("SELECT status FROM Application WHERE app_id = '"
										+meantableAppID+"'");
				String status = null;
				while( rs.next() ){
					status= rs.getString("status");
				}
				
				 String street = null;
				 String city = null;
				 String lstate =null;
				 String zip = null;
				 String areaCode =null;
				 String pnumber = null;

				rs = stmt.executeQuery("SELECT AddID FROM MainTable WHERE NameID = '"
							+ app +"'");
				int maintableaddid = -1;
				while(rs.next()){
					maintableaddid = rs.getInt("AddID");
				}
				 
				rs = stmt.executeQuery("SELECT Street, City, State, Zip, AreaCode, Phone FROM ADDRESS WHERE AddID = '"
							+maintableaddid+"'");
				 
				while(rs.next()){
					street = rs.getString("Street");
					city = rs.getString("City");
					lstate = rs.getString("State");
					zip = rs.getString("Zip");
					areaCode = rs.getString("AreaCode");
					pnumber = rs.getString("Phone");
				}
				
				String citizen = null;
				rs = stmt.executeQuery("SELECT Country FROM Countries WHERE ID=  "
						+"(SELECT Citizenship FROM COUNTRY WHERE CounID = "
						+"(SELECT distinct CounID FROM MainTable WHERE NameID = "
						+"(SELECT NAME.NAMEID FROM NAME WHERE First = '"
						+first+"' AND Last = '"+ last+"')))");
			 
				while(rs.next()){
					citizen = rs.getString("Country");
				}
				
				String residency =null;
				rs = stmt.executeQuery("SELECT Residency FROM COUNTRY WHERE CounID = "
						+"(SELECT distinct CounID FROM MainTable WHERE NameID = "
						+"(SELECT NAME.NAMEID FROM NAME WHERE First = '"
						+first+"' AND Last = '"+ last+"'))");
			 
				while(rs.next()){
					residency = rs.getString("Residency");
				}
				
				String residence = null;
				rs = stmt.executeQuery("SELECT Country FROM Countries WHERE ID=  "
						+"(SELECT Residence FROM COUNTRY WHERE CounID = "
						+"(SELECT distinct CounID FROM MainTable WHERE NameID = "
						+"(SELECT NAME.NAMEID FROM NAME WHERE First = '"
						+first+"' AND Last = '"+ last+"')))");
			 
				while(rs.next()){
					residence = rs.getString("Country");
				}
				
				
				String specialization = null;
				rs = stmt.executeQuery("SELECT Specialization FROM Specializations WHERE ID=  "
						+"(SELECT distinct SpecID FROM MainTable WHERE NameID = "
						+"(SELECT NAME.NAMEID FROM NAME WHERE First = '"
						+first+"' AND Last = '"+ last+"'))");
			 
				while(rs.next()){
					specialization = rs.getString("Specialization");
				}
				
				
				rs1 = stmt1.executeQuery("SELECT DegID FROM MainTable WHERE NameID = '"
						+ app + "'");
				while(rs1.next()){
				
						rs = stmt.executeQuery("SELECT * FROM DEGREE WHERE DegID= '"
								+rs1.getInt("DegID") +"'");
						rs2 = stmt2.executeQuery("SELECT Location, University FROM Universities WHERE ID = "
								+"(SELECT School FROM DEGREE WHERE DegID=  '"
								+rs1.getInt("DegID") +"')");

						rs3 = stmt3.executeQuery("SELECT Discipline FROM Disciplines WHERE ID = "
								+"(SELECT Major FROM DEGREE WHERE DegID=  '"
								+rs1.getInt("DegID") +"')");

					 
						while(rs.next()&&rs2.next()&&rs3.next()){
							forms.degreecollection newDegree = new degreecollection(rs2.getString("Location"),rs2.getString("University"),
									rs3.getString("Discipline"),rs.getString("Month"),rs.getString("Year"),rs.getString("GPA"),rs.getString("Title"),
									rs.getString("Trans_name"),rs.getBinaryStream("Trans_file"),rs.getInt("Trans_size"));
							lo.updateCollection(newDegree);
						}
				}
				
				lo.setSpecialization(specialization);
				lo.setResidence(residence);
				lo.setResidency(residency);
				lo.setCitizen(citizen);
				lo.setFirst(first);
				lo.setLast(last);
				lo.setMiddle(middle);
				lo.setAreaCode(areaCode);
				lo.setStatus(status);
				lo.setCity(city);
				lo.setZip(zip);
				lo.setLstate(lstate);
				lo.setPnumber(pnumber);
				lo.setStreet(street);
				
				
				rs.close();
				stmt.close();
				rs2.close();
				stmt2.close();
				rs3.close();
				stmt3.close();
				conn.close();
					
			} catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			
			return (mapping.findForward("retApplicant"));	
		}
	}
}

